package com.library.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.mysql.jdbc.Connection;

public class MySQLBookItemDAO implements BookItemDAO {

	@Override
	public boolean insert(BookItem book) {
		Connection conn = MySQLConnection.getConnection();
		if (conn != null) {
			boolean ok = true;
			String query = "INSERT INTO `bookitems` (`bookid`, `booktitleid`, `status`) VALUES ("
					+ book.getBookId()
					+ ","
					+ book.getBookTitleId()
					+ ",'"
					+ book.getStatus() + "')";
			try {
				Statement statement = conn.createStatement();
				statement.executeUpdate(query);
			} catch (SQLException e) {
				ok = false;
			} finally {
				MySQLConnection.closeConnection(conn);
			}
			return ok;
		} else {
			return false;
		}
	}

	@Override
	public boolean remove(int bookId) {
		Connection conn = MySQLConnection.getConnection();
		if (conn != null) {
			boolean ok = true;
			String query = "DELETE FROM `bookitems` WHERE `bookid` = " + bookId +"";
			try {
				Statement statement = conn.createStatement();
				statement.executeUpdate(query);
			} catch (SQLException e) {
				ok = false;
			} finally {
				MySQLConnection.closeConnection(conn);
			}
			return ok;
		} else {
			return false;
		}
	}

	@Override
	public boolean update(BookItem book) {
		Connection conn = MySQLConnection.getConnection();
		if (conn != null) {
			boolean ok = true;
			String query = "UPDATE `bookitems` SET "
					+ "`booktitleid` =" + book.getBookTitleId() + ","
					+ "`status` ='" + book.getStatus() + "'"
					+ " WHERE "
					+ "`bookid` =" + book.getBookId();
			try {
				Statement statement = conn.createStatement();
				statement.executeUpdate(query);
			} catch (SQLException e) {
				ok = false;
			} finally {
				MySQLConnection.closeConnection(conn);
			}
			return ok;
		} else {
			return false;
		}
	}

	@Override
	public BookItem findByKey(int bookId) {
		Connection conn = MySQLConnection.getConnection();
		BookItem book = null;
		if (conn!= null){
			String querry = "SELECT * FROM `bookitems` WHERE bookid = " + bookId;
			try {
				Statement stmt = conn.createStatement() ;
				ResultSet result = stmt.executeQuery(querry);
				if (result.next()){
					book = new BookItem();
					book.setBookId(bookId);
					book.setBookTitleId(result.getInt("booktitleid"));
					book.setStatus(result.getString("status"));

				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally{
				MySQLConnection.closeConnection(conn);
			}

		}
		return book;
	}

	@Override
	public ArrayList<BookItem> findAll() {
		ArrayList<BookItem> books = null;
		Connection conn = MySQLConnection.getConnection();
		if (conn!= null){
			String querry = "SELECT * FROM `bookitems` WHERE 1";
			try {
				Statement stmt = conn.createStatement() ;
				ResultSet result = stmt.executeQuery(querry);
				books = new ArrayList<BookItem>();
				while (result.next()){
					BookItem book = new BookItem();
					book.setBookId(result.getInt("bookid"));
					book.setBookTitleId(result.getInt("booktitleid"));
					book.setStatus(result.getString("status"));
					books.add(book);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally{
				MySQLConnection.closeConnection(conn);
			}

		}
		return books;
	}

}
